package cn.edu.zut.cs.Impl;

import cn.edu.zut.cs.DAO.SCDAO;
import cn.edu.zut.cs.Model.SC;
import cn.edu.zut.cs.MyConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SCDAOImpl implements SCDAO {

    @Override
    public List<SC> getAllSCs()  {
        List<SC> l = new ArrayList<>();
        try {
            //获取连接，静态方法

            Connection conn = MyConnection.getConnection();

            String sql = "select * from sc";
            //连接上面创建一个容器
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);


            while(rs.next()) {

                SC s = new SC();
                s.setSno(rs.getString("Sno"));
                s.setCno(rs.getString("Cno"));
                s.setGrade(rs.getInt("Grade"));
                l.add(s);
            }
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return l;
    }

    @Override
    public void insert(SC s) {

        try {
            Connection conn = MyConnection.getConnection();

            String sql = "insert into sc (Sno, Cno, Grade) values('"
                    + s.getSno() + "','"
                    + s.getCno() + "',"
                    + s.getGrade() + ")";
//            System.out.println(sql);
            Statement stat = conn.createStatement();
            System.out.println(sql);
            stat.executeUpdate(sql);
            conn.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public SC selectOne(SC s) {
        SC bok = null;
        try {
            Connection conn = MyConnection.getConnection();

            String sql = "select * from sc where isdn='"
                    + s.getSno() +"'";
            System.out.println(sql);
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                s.setSno(rs.getString("Sno"));
                s.setCno(rs.getString("Cno"));
                s.setGrade(rs.getInt("Grade"));

            }
            conn.close();

        }catch (Exception e) {}


        return bok;
    }
}
